package com.wsh.server.sqlserver;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class NoSetMain {
    private static final String DB_URL = "jdbc:sqlserver://58.246.84.206:11433;SelectMethod=cursor;DataBaseName=ttl;useSSL=false";
    private static final String DB_USER = "sa";
    private static final String DB_PASSWORD = "qwe20230613.";
    private static final String DB_DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    private static final String PATH = "src/main/resources/sql2/";//目录
    private static final String DB_NAME = "mes_db.";//被替换的数据库前缀名称
    private static boolean isAppend = true;//是否附加前后缀(SET IDENTITY_INSERT dbo." + tableName + " ON;)
//TB_PRODUCT_PROCESS_TEST_202401021557.sql.sql.sql

    public static void main(String[] args) {
        try {
            // 1. 加载驱动程序
            Class.forName(DB_DRIVER);
            // 2. 建立连接
            Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
            // 获取文件夹对象
            File folder = new File(PATH);
            // 获取文件夹中所有的文件对象（包括子文件夹）
            File[] files = folder.listFiles();
            int i = 1;
            // 循环遍历文件对象数组
            for (File file : files) {
                // 如果文件对象是文件（而不是文件夹）
                if (file.isFile()) {
                    long startTime = System.currentTimeMillis();
                    // 获取表名
                    String tableName = file.getName().substring(0,file.getName().lastIndexOf("_"));
                    String inputFilePath = PATH + file.getName();
                    System.out.println(i + ":" + tableName);
                    String sql = "";
                    // 3.读取输入文件内容
                    try {
                        BufferedReader reader = new BufferedReader(new FileReader(inputFilePath));
                        String line = reader.readLine();
                        StringBuilder sqlContent = new StringBuilder();
                        while (line != null) {
                            sqlContent.append(line);
                            sqlContent.append(" ");
                            line = reader.readLine();
                        }
                        reader.close();
                        // 去除换行符并以";"结尾为一行
                        sql = sqlContent.toString().replace("\n", " ").trim() + ";" + "\n";

                        sql = sql.replace(DB_NAME, "");
                        String begin = "SET IDENTITY_INSERT dbo." + tableName + " ON;\n";
                        String end = "SET IDENTITY_INSERT dbo." + tableName + " OFF;";
                        String truncate = "TRUNCATE TABLE dbo." + tableName + ";\n";
                        // String truncate = "";
                        if (isAppend){
                            sql = truncate + begin + sql + end;
                        }else {
                            sql = truncate  + sql ;
                        }
                        if (!sql.isEmpty()) {
                            try {
                                PreparedStatement stmt = conn.prepareStatement(sql);
                                stmt.executeUpdate();
                                // 移动文件
                                // move(inputFilePath, SUCCESS_PATH);
                                delete(inputFilePath);
                                // System.out.println("成功导入 " + tableName + " 表");
                            } catch (SQLException e) {
                                // 移动文件
                                // move(inputFilePath, ERROR_PATH);
                                // delete(inputFilePath);
                                System.out.println("导入失败 " + tableName + " 表"+ e.getMessage());
                            }
                        }
                        // long endTime = System.currentTimeMillis();
                        // long duration = endTime - startTime;
                        // System.out.println("第"+i+"张表("+tableName+")导入耗时：" + duration + "毫秒");
                        i++;
                        // 5. 关闭连接和文件流
                        reader.close();
                    } catch (Exception e) {
                        System.out.println("读取异常:" + file.getName()+ e.getMessage());
                    }


                }
            }


            conn.close();
        } catch (ClassNotFoundException e) {
            System.out.println("异常" + e.getMessage());
        } catch (Exception e) {
            System.out.println("异常" + e.getMessage());
        }
    }

    /**
     * 移动文件
     *
     * @param sourceFilePath
     * @param destinationDirectoryPath
     */
    private static void move(String sourceFilePath, String destinationDirectoryPath) {
        // String sourceFilePath = "path/to/source/file.txt";
        // String destinationDirectoryPath = "path/to/destination/directory";

        try {
            Path sourceFile = Paths.get(sourceFilePath);
            Path destinationDirectory = Paths.get(destinationDirectoryPath);

            Files.move(sourceFile, destinationDirectory);

            System.out.println("文件已成功移动到目标目录");
        } catch (IOException e) {
            System.err.println("移动文件时出错: " + e.getMessage());
        }
    }

    /**
     * 删除文件
     *
     * @param filePath
     */
    private static void delete(String filePath) {
        // String filePath = "path/to/file.txt";

        try {
            Files.delete(Paths.get(filePath));

            System.out.println("文件已成功删除");
        } catch (IOException e) {
            System.err.println("删除文件时出错: " + e.getMessage());
        }
    }
}